﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsPurchaseDataAccess
    {
        private static string GetBodySQL()
        {
            return @"SELECT a.* ,b.MaterialRequirementID, b.WorkOrderNo, b.NeedNum
,d.PurchaseNum AS HasPurNum,b.MatApplyNum-isnull(d.PurchaseNum,0) AS ResNum
,c.MaterialName, c.Specifications, c.Unit,b.MatApplyNum
FROM BS_PurchaseDetail a
LEFT JOIN BS_MatApplyDetail b ON b.MatApplyDetailID=a.MatApplyDetailID
LEFT JOIN MD_Material c ON c.MaterialID=A.MaterialID
LEFT JOIN (SELECT aa.MatApplyDetailID,SUM(aa.PurchaseNum) AS PurchaseNum
           FROM BS_PurchaseDetail aa
           GROUP BY aa.MatApplyDetailID) d ON d.MatApplyDetailID=b.MatApplyDetailID
WHERE 1=1";
        }


        public static DataTable SelectBody(string strCode, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = GetBodySQL() + " AND A.BillID=@BillID";
            dpc.AddWithValue("@BillID", strCode);
            return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
        }

        public static decimal SelectResNum(string strRquisitionDetailID, string strCode)
        {
            string strtep = "";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(strCode))
            {
                strtep = " AND aa.BillID<>@BillID";
                dpc.AddWithValue("@BillID", strCode);

            }
            string strcmd = string.Format(@"select a.Nums,PurchaseNum from BS_RquisitionDetail a
LEFT JOIN (SELECT aa.RquisitionDetailID,SUM(aa.Nums) AS PurchaseNum
           FROM BS_PurchaseDetail aa
           WHERE 1=1{0}
           GROUP BY aa.RquisitionDetailID) b ON b.RquisitionDetailID=a.RquisitionDetailID
WHERE a.Nums-ISNULL(b.PurchaseNum,0)>0
AND a.BillDetailID=@BillDetailID", strtep);
            dpc.AddWithValue("@BillDetailID", strRquisitionDetailID);

            return ClsCommDA.ExeDecimal(strcmd, dpc);
        }

        public static DataTable SelectMaterial(string MaterialID, string MaterialName, string IsRquisition, string RquisitionID, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select m.*,NULL AS RquisitionDetailID,NULL AS RquisitionID,
                                        NULL AS RquisitionOrderNum ,NULL AS WorkOrderNo 
                                        ,NULL AS RquisitionNums ,NULL AS RquisitionMMNumber ,NULL AS ResNum 
                                        from MD_Material m where 1=1 ";
                    if (!string.IsNullOrEmpty(IsRquisition) && IsRquisition == "1")
                    {
                        strcmd = @"select rd.BillDetailID as RquisitionDetailID,a.BillID as RquisitionID,
                                            rd.Nums as RquisitionNums,rd.WorkOrderNo,
                                            rd.MMNumber as RquisitionMMNumber,rd.MaterialID,b.RecNum,
                                            m.MaterialName,m.Specifications,m.Unit,m.Price,m.MMNumber,m.MeterWeight,
                                    isnull(rd.Nums,0)-isnull(RecNum,0) as ResNum 
                                    from BS_Rquisition a
                                    left join BS_RquisitionDetail rd on rd.BillID=a.BillID
                                    LEFT JOIN (SELECT aa.RquisitionDetailID,SUM(aa.Nums) AS RecNum
                                               FROM BS_PurchaseDetail aa
                                               WHERE 1=1 and aa.BillID<>@RquisitionID
                                               GROUP BY aa.RquisitionDetailID) b ON b.RquisitionDetailID=rd.BillDetailID
                                    left join MD_Material m on m.MaterialID=rd.MaterialID
                                    where isnull(rd.Nums,0)-isnull(RecNum,0)>0  ";
                        dpc.AddWithValue("@RquisitionID", RquisitionID);
                    }
                    if (!string.IsNullOrEmpty(MaterialID))
                    {
                        strcmd += " and m.MaterialID like @MaterialID";
                        dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
                    }
                    if (!string.IsNullOrEmpty(MaterialName))
                    {
                        strcmd += " and m.MaterialName like @MaterialName";
                        dpc.AddWithValue("@MaterialName", "%" + MaterialName + "%");
                    }
                    strcmd += " order by m.MaterialID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectList(string strBillID, string strFrom, string strTo, string strWorkOrderNo, string strMaterialName, int IPurchaseCount, out int icurPageIndex, int pageIndex, int pageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_PurchaseDetail A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and A.BillID LIKE @PurchaseID";
                    dpc.AddWithValue("@PurchaseID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and A.WorkOrderNo LIKE @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and A.MaterialName LIKE @MaterialName";
                    dpc.AddWithValue("@MaterialName", "%" + strMaterialName + "%");
                }


                strcmd += " ORDER BY A.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, pageSize, dpc, IPurchaseCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable GetDataByBillID(string id)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select p.*,s.SupplierName,pd.BillDetailID,pd.OrderNum,pd.RquisitionID,pd.RquisitionDetailID, 
		                                    pd.WorkOrderNo,pd.MaterialID,pd.Nums,pd.MMNumber,pd.Price,pd.Amount,
		                                    pd.ExpectDate,m.MaterialName,m.Specifications,m.Unit,m.MMNumber as SingleMMNumber,
		                                    r.Nums as RquisitionNums,c.PurTotalNums,ISNULL(r.Nums,0)-ISNULL(c.PurTotalNums,0) as ResNum
                                    from BS_Purchase p
                                    left join BS_PurchaseDetail pd on pd.BillID=p.BillID
                                    left join MD_Material m on m.MaterialID=pd.MaterialID
                                    left join MD_Supplier s on s.SupplierID=p.SupplierID
                                    left join BS_RquisitionDetail r on r.BillDetailID=pd.RquisitionDetailID
                                    LEFT join (select RquisitionDetailID,MaterialID,SUM(ISNULL(Nums,0)) as PurTotalNums 
			                                    from BS_PurchaseDetail
			                                    where BillID<>@PurchaseID
			                                    group by RquisitionDetailID,MaterialID) c on c.MaterialID=pd.MaterialID and c.RquisitionDetailID=pd.RquisitionDetailID
                                    where p.BillID=@PurchaseID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@PurchaseID", id);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
